package Tax_DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mva.common.DateUtils;
import com.mva.database.DB_Pipe;

public class TaxDB implements TaxDAO
  {
   private Connection con = null;
	   
   public TaxDB()
	 {
	 }

   public boolean getTaxes (ArrayList<Tax> taxes)
	 {
	  try
		{
		 String qry = "SELECT State, "         +
		                     "County, "        +
		                     "City, "          +
		                     "Dt_Eff, "        +
		                     "Rt "             +
			 	        "FROM tax "            +
				       "ORDER BY State  ASC, " +
				                "County ASC, " +
				                "City   ASC, " +
				                "Dt_Eff DESC";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(qry);
		 ResultSet         rs  = ps.executeQuery();
		 while(rs.next())
		   {
		    String State  = rs.getString("State");
		    String County = rs.getString("County");
		    String City   = rs.getString("City");
		    Date   Dt_Eff = rs.getDate("Dt_Eff");
		    double Rt     = rs.getDouble("Rt");
		    Tax    t      = new Tax(State, County, City, Dt_Eff, Rt);
		    taxes.add(t);
		   }
		 rs.close();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	 }

   public Tax getTax (String state, String county, String city, Date effDt)
	 {
	  try
		{
		 String sel = "SELECT State, "         +
		                     "County, "        +
		                     "City, "          +
		                     "Dt_Eff, "        +
		                     "Rt "             +
			 	        "FROM tax "            +
				       "WHERE State = ? "      +
				         "AND County = ? "     +
				         "AND City   = ? "     +
				         "AND Dt_Eff <= ? "    + 
      	               "ORDER BY State  ASC, " +
                                "County ASC, " +
                                "City   ASC, " +
                                "Dt_Eff DESC";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(sel);
		 ps.setString(1, state);
		 ps.setString(2, county);
		 ps.setString(3, city);
		 ps.setDate(4, effDt);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String State  = rs.getString("State");
		    String County = rs.getString("County");
		    String City   = rs.getString("City");
		    Date   Dt_Eff = rs.getDate("Dt_Eff");
		    double Rt     = rs.getDouble("Rt");
		    Tax    t      = new Tax(State, County, City, Dt_Eff, Rt);
			rs.close();
			ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return t;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }
   
   public boolean existsTax (String state, String county, String city, Date effDt)
     {
	  String tmpState  = state.trim();
             tmpState  = tmpState.toUpperCase();
      String tmpCounty = county.trim();
             tmpCounty = tmpCounty.toUpperCase();
      String tmpCity   = city.trim();
             tmpCity   = tmpCity.toUpperCase();
	  try
		{
		 String exists = "SELECT Rt "             +
	                       "FROM tax "            +
                          "WHERE State  =  ? "    +
                            "AND County =  ? "    +
                            "AND City   =  ? "    +
                            "AND Dt_Eff <= ? "    + 
                          "ORDER BY State  ASC, " +
                                   "County ASC, " +
                                   "City   ASC, " +
                                   "Dt_Eff DESC";
	                       con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setString(1, tmpState);
		 ps.setString(2, tmpCounty);
		 ps.setString(3, tmpCity);
		 ps.setDate(4, effDt);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   return false;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean addTax (Tax t)
	 {
	  try
		{
		 String ins = "INSERT "                  +
		                "INTO tax (State, "      +
		                           "County, "    +
		                           "City, "      +
		                           "Dt_Eff, "    +
		                           "Rt, "        +
		                           "TMS_Updt, "  +
		                           "User_Updt, " +
		                           "TMS_Crtd, "  +
		                           "User_Crtd) " +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(ins);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setString(1, t.getState());
		 ps.setString(2, t.getCounty());
		 ps.setString(3, t.getCity());
		 ps.setDate(4, t.getDtEff());
		 ps.setDouble(5, t.getRt());
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteTax (Tax t)
	 {
	  try
		{
		 String del = "DELETE "              +
		                "FROM tax "          +
                       "WHERE State  =  ? "  +
                         "AND County =  ? "  +
                         "AND City   =  ? "  +
                         "AND Dt_Eff <= ? "; 
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(del);
		 ps.setString(1, t.getState());
		 ps.setString(2, t.getCounty());
		 ps.setString(3, t.getCity());
		 ps.setDate(4, t.getDtEff());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateTax (Tax t)
	 {
	  try
		{
		 String upd = "UPDATE tax "            +
		                 "SET Rt        = ?, " +
		                     "TMS_Updt  = ?, " +
					         "User_Updt = ? "  +
                       "WHERE State  =  ? "    +
                         "AND County =  ? "    +
                         "AND City   =  ? "    +
                         "AND Dt_Eff <= ? "; 
                           con = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(upd);
	     Timestamp         ts  = DateUtils.getTmstmp();
		 ps.setDouble(1, t.getRt());
		 ps.setTimestamp(2, ts);
		 ps.setString(3, "USER8888");
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }
  }
